# -*- coding: utf-8 -*-
# Author: Andeviln
# Date: 2025/01/6
# Version: 1.0.0
# 功能：SQLite数据库的连接、创建表、插入数据、查询数据、关闭连接

import sqlite3
import os
import config

class SQLite:
    def __init__(self):
        conf = config.Config()
        self.db_dir_path  = conf.get(section='Paths', key='DATA_FILE_PATH')
        self.db_file_name = conf.get(section='FileNames', key='DATA_FILE_NAME')
        self.db_path = os.path.join(self.db_dir_path, self.db_file_name)
        # print(self.db_path)
        self.conn = None
        self.cursor = None
        self.connect()

    def connect(self): # 连接数据库
        try:
            self.conn = sqlite3.connect(self.db_path)
            self.cursor = self.conn.cursor()
        except sqlite3.Error as e:
            print("Error connecting to database: ", e)
            exit(1)
    def create_table(self, table_name, columns): # 创建表
        try:
            # columns为列名和类型，例如：'id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER'
            self.cursor.execute(f"CREATE TABLE IF NOT EXISTS {table_name} ({columns})")
            self.conn.commit()
        except sqlite3.Error as e:
            print("Error creating table: ", e)
            exit(1)
    def insert_data(self, table_name, data): # 插入数据
        try:
            self.cursor.execute(f"INSERT INTO {table_name} VALUES ({','.join(['?']*len(data))})", data)
            self.conn.commit()
        except sqlite3.Error as e:
            print("Error inserting data: ", e)
            exit(1)
    def query_data(self, table_name, condition=None): # 查询数据
        try:
            if condition: # 如果有条件，则加上WHERE子句
                self.cursor.execute(f"SELECT * FROM {table_name} WHERE {condition}")
            else:
                self.cursor.execute(f"SELECT * FROM {table_name}")
            return self.cursor.fetchall()
        except sqlite3.Error as e:
            print("Error querying data: ", e)
            exit(1)
    def close(self): # 关闭连接
        try:
            self.cursor.close()
            self.conn.close()
        except sqlite3.Error as e:
            print("Error closing database: ", e)
            exit(1)
    def __del__(self):
        self.close()
        # if os.path.exists(self.db_path):
        #     os.remove(self.db_path)
        #     print("Database closed and deleted.")
        #     # exit(0)
    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()
        # if os.path.exists(self.db_path):
        #     os.remove(self.db_path)
        #     print("Database closed and deleted.")

if __name__ == '__main__':
    db = SQLite()
    db.create_table('test', 'id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER')
    db.insert_data('test', (1,'Andeviln', 18))
    print(db.query_data('test'))